package uit.jee.lib.access.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.jee.lib.dto.BookRequestDTO;

/**
 *
 * @author ngloc_it
 */
public class BookRequestMapper extends MapperDB{

    public BookRequestMapper() throws Exception {
        super();
    }

    private void InitBookRequestDTOFromRs(BookRequestDTO bookRe, ResultSet rs) throws Exception{
        if((rs != null) && (bookRe != null)){
            bookRe.setRequestId(rs.getString(BookRequestDTO.BOOK_REQUEST_ID));
            bookRe.setBookId(rs.getString(BookRequestDTO.BOOK_REQUEST_BOOK_ID));
            bookRe.setReaderId(rs.getString(BookRequestDTO.BOOK_REQUEST_READER_ID));
            bookRe.setDate(rs.getString(BookRequestDTO.BOOK_REQUEST_DATE));
            bookRe.setBookName(rs.getString(BookRequestDTO.BOOK_REQUEST_BOOK_NAME));
            bookRe.setReaderName(rs.getString(BookRequestDTO.BOOK_REQUEST_READER_NAME));
        }
    }

    public boolean CheckExistedRequest(BookRequestDTO req) throws Exception {
        boolean isExisted = false;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT booklibrary.yeucaumuonsach where ");
            sql.append(BookRequestDTO.BOOK_REQUEST_ID).append(" = '");
            sql.append(req.getRequestId()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                isExisted = true;
            }
        } catch (Exception ex) {
            throw ex;
        }

        return isExisted;
    }

    public ArrayList<BookRequestDTO> GetAllRequest() throws Exception {
        ArrayList<BookRequestDTO> bookrequests = new ArrayList<BookRequestDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT booklibrary.yeucaumuonsach.MaYeuCau, "
                    + "booklibrary.yeucaumuonsach.MaSach, "
                    + "booklibrary.yeucaumuonsach.MaDocGia, "
                    + "booklibrary.yeucaumuonsach.NgayYeuCau, "
                    + "booklibrary.yeucaumuonsach.TinhTrang, "
                    + "booklibrary.sach.TenSach, "
                    + "booklibrary.docgia.TenDocGia "
                    + "FROM booklibrary.sach , booklibrary.docgia , booklibrary.yeucaumuonsach "
                    + "WHERE booklibrary.yeucaumuonsach.MaSach = booklibrary.sach.MaSach AND "
                    + "booklibrary.yeucaumuonsach.MaDocGia = booklibrary.docgia.MaDocGia");
            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            while (re != null && re.next()) {
                BookRequestDTO bookrequest = new BookRequestDTO();
                InitBookRequestDTOFromRs(bookrequest, re);
                bookrequests.add(bookrequest);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return bookrequests;
    }

    public ArrayList<BookRequestDTO> GetRequestUnProcess() throws Exception {
        ArrayList<BookRequestDTO> bookrequests = new ArrayList<BookRequestDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("select * from booklibrary.yeucaumuonsach where");
            sql.append("TinhTrang = ").append(BookRequestDTO.BOOK_REQUEST_STATUS_UNPROCESS);

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmt.executeQuery();

            while (re != null && re.next()) {
                BookRequestDTO bookre = new BookRequestDTO();
                InitBookRequestDTOFromRs(bookre, re);
                bookrequests.add(bookre);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return bookrequests;
    }

    public ArrayList<BookRequestDTO> GetRequestProcessed() throws Exception {
        ArrayList<BookRequestDTO> bookrequests = new ArrayList<BookRequestDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("select * from booklibrary.yeucaumuonsach where");
            sql.append("TinhTrang = ").append(BookRequestDTO.BOOK_REQUEST_STATUS_PROCESSED);

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmt.executeQuery();

            while (re != null && re.next()) {
                BookRequestDTO bookre = new BookRequestDTO();
                InitBookRequestDTOFromRs(bookre, re);
                bookrequests.add(bookre);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return bookrequests;
    }

    public BookRequestDTO GetRequest(String requestId) throws Exception {
        BookRequestDTO bookrequests = new BookRequestDTO();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("select * from booklibrary.yeucaumuonsach where");
            sql.append("MaYeuCau = '").append(requestId).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmt.executeQuery();

            if (re != null && re.next()) {

                InitBookRequestDTOFromRs(bookrequests, re);

            }
        } catch (Exception ex) {
            throw ex;
        }
        return bookrequests;
    }

    public void Insert(BookRequestDTO bookRe) throws Exception {

        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into booklibrary.yeucaumuonsach values( ");
            sql.append("'").append(bookRe.getRequestId()).append("', ");
            sql.append("'").append(bookRe.getBookId()).append("', ");
            sql.append("'").append(bookRe.getReaderId()).append("', ");
            sql.append("'").append(bookRe.getDate()).append("', ");
            sql.append(bookRe.getStatus()).append(")");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception e) {
            throw e;
        }
    }

    public void Insert(BookRequestDTO[] bookRes) throws Exception {
    }

    public void Update(BookRequestDTO bookRe) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Update booklibrary.yeucaumuonsach Set ");
            sql.append(BookRequestDTO.BOOK_REQUEST_ID).append("='").append(bookRe.getRequestId()).append("', ");
            sql.append(BookRequestDTO.BOOK_REQUEST_BOOK_ID).append("='").append(bookRe.getBookId()).append("', ");
            sql.append(BookRequestDTO.BOOK_REQUEST_READER_ID).append("='").append(bookRe.getReaderId()).append("', ");
            sql.append(BookRequestDTO.BOOK_REQUEST_DATE).append("='").append(bookRe.getDate()).append("', ");
            sql.append("TinhTrang =").append(bookRe.getStatus());

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public void Update(BookRequestDTO[] bookRes) throws Exception {
    }

    public void Delete(BookRequestDTO bookRe) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("delete * from booklibrary.yeucaumuonsach where ");
            sql.append(BookRequestDTO.BOOK_REQUEST_ID).append(" ='").append(bookRe.getRequestId()).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public void Delete(BookRequestDTO[] bookRe) throws Exception {
    }

    public void Delete(String requestId) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("delete * from booklibrary.yeucaumuonsach where ");
            sql.append(BookRequestDTO.BOOK_REQUEST_ID).append(" ='").append(requestId).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }
}
